"""
将 JSON 数据导入到 SQLite 数据库的 paragraphs_n 表
"""
import json
import sqlite3
from pathlib import Path
from typing import List, Dict, Optional


def init_table(db_path: str):
    """
    初始化 paragraphs_n 表
    
    Args:
        db_path: 数据库文件路径
    """
    print("=" * 80)
    print("📊 初始化 paragraphs_n 表...")
    print("=" * 80)
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # 删除旧表（如果存在）
        cursor.execute("DROP TABLE IF EXISTS paragraphs_n")
        print("   ✅ 已删除旧表 paragraphs_n（如果存在）")
        
        # 创建新表
        create_table_sql = """
        CREATE TABLE paragraphs_n (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_code TEXT,
            book_name TEXT,
            category TEXT,
            page_num INTEGER,
            page_id TEXT,
            title TEXT,
            heaven_stem TEXT,
            earth_branch TEXT,
            title_keywords TEXT,
            pid TEXT,
            order_num INTEGER,
            original_text TEXT,
            translated_text TEXT,
            annotations TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        cursor.execute(create_table_sql)
        print("   ✅ 已创建表 paragraphs_n")
        
        # 创建索引
        indexes = [
            ("idx_book_code_n", "book_code"),
            ("idx_book_name_n", "book_name"),
            ("idx_page_id_n", "page_id"),
            ("idx_title_keywords_n", "title_keywords"),
            ("idx_heaven_stem_n", "heaven_stem"),
            ("idx_earth_branch_n", "earth_branch"),
            ("idx_pid_n", "pid"),
        ]
        
        for idx_name, column in indexes:
            cursor.execute(f"CREATE INDEX IF NOT EXISTS {idx_name} ON paragraphs_n({column})")
            print(f"   ✅ 已创建索引 {idx_name}")
        
        conn.commit()
        print()
        print("✅ 表初始化完成")
        
    except Exception as e:
        conn.rollback()
        print(f"❌ 初始化表失败: {e}")
        raise
    finally:
        conn.close()


def import_data(db_path: str, json_path: str):
    """
    导入 JSON 数据到数据库
    
    Args:
        db_path: 数据库文件路径
        json_path: JSON 文件路径
    """
    print("=" * 80)
    print("📥 开始导入数据...")
    print("=" * 80)
    print(f"📁 JSON 文件: {json_path}")
    print(f"💾 数据库: {db_path}")
    print()
    
    # 读取 JSON 文件
    print("📖 正在读取 JSON 文件...")
    with open(json_path, 'r', encoding='utf-8') as f:
        records = json.load(f)
    
    print(f"   ✅ 读取了 {len(records)} 条记录")
    print()
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 插入 SQL
    insert_sql = """
    INSERT INTO paragraphs_n (
        book_code, book_name, category, page_num, page_id,
        title, heaven_stem, earth_branch, title_keywords,
        pid, order_num, original_text, translated_text, annotations
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    
    try:
        success_count = 0
        error_count = 0
        
        # 使用事务批量插入
        print("📝 正在导入数据...")
        
        for idx, record in enumerate(records, 1):
            try:
                # 处理 annotations（如果是列表，转换为 JSON 字符串）
                annotations = record.get('annotations')
                if annotations is not None:
                    if isinstance(annotations, list):
                        annotations = json.dumps(annotations, ensure_ascii=False)
                    elif not isinstance(annotations, str):
                        annotations = json.dumps(annotations, ensure_ascii=False)
                else:
                    annotations = None
                
                # 准备数据
                values = (
                    record.get('book_code'),
                    record.get('book_name'),
                    record.get('category'),
                    record.get('page_num'),
                    record.get('page_id'),
                    record.get('title'),
                    record.get('heaven_stem'),
                    record.get('earth_branch'),
                    record.get('title_keywords'),
                    record.get('pid'),
                    record.get('order_num'),
                    record.get('original_text'),
                    record.get('translated_text'),
                    annotations
                )
                
                # 插入数据
                cursor.execute(insert_sql, values)
                success_count += 1
                
                # 每1000条提交一次
                if idx % 1000 == 0:
                    conn.commit()
                    print(f"   ✅ 已导入 {idx}/{len(records)} 条记录...")
                
            except Exception as e:
                error_count += 1
                print(f"   ❌ 第 {idx} 条记录导入失败: {e}")
                if error_count > 10:
                    print(f"   ⚠️  错误过多，停止导入")
                    break
        
        # 提交剩余数据
        conn.commit()
        
        print()
        print("=" * 80)
        print("📊 导入统计")
        print("=" * 80)
        print(f"✅ 成功: {success_count} 条")
        print(f"❌ 失败: {error_count} 条")
        print(f"📄 总计: {len(records)} 条")
        
        # 验证数据
        cursor.execute("SELECT COUNT(*) FROM paragraphs_n")
        db_count = cursor.fetchone()[0]
        print(f"💾 数据库中的记录数: {db_count} 条")
        
        print("=" * 80)
        
    except Exception as e:
        conn.rollback()
        print(f"❌ 导入失败: {e}")
        raise
    finally:
        conn.close()


def main():
    """主函数"""
    import sys
    
    # 数据库路径（相对于项目根目录）
    project_root = Path(__file__).parent.parent.parent
    db_path = project_root / "bazi-server" / "bazi_data.db"
    db_path = db_path.resolve()
    
    # JSON 文件路径
    json_path = Path(__file__).parent / "output.json"
    json_path = json_path.resolve()
    
    # 检查文件是否存在
    if not db_path.exists():
        print(f"❌ 数据库文件不存在: {db_path}")
        sys.exit(1)
    
    if not json_path.exists():
        print(f"❌ JSON 文件不存在: {json_path}")
        sys.exit(1)
    
    try:
        # 初始化表
        init_table(str(db_path))
        print()
        
        # 导入数据
        import_data(str(db_path), str(json_path))
        
        print()
        print("🎉 导入完成！")
        
    except Exception as e:
        print(f"❌ 发生错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)


if __name__ == '__main__':
    main()

